<?php
/*
 * @Author: your name
 * @Date: 2021-11-03 15:16:15
 * @LastEditTime: 2021-11-19 09:56:08
 * @LastEditors: Please set LastEditors
 * @Description: 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 * @FilePath: \property\app\Models\admin_model\Column.php
 */

namespace App\Models\admin_model;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\Calculation\Statistical\Distributions\F;

class Column extends Model
{
    use HasFactory;
    static string $tableName = 'column';

    public static function columnAll()
    {
        return DB::table(self::$tableName)->select()->get()->toArray();
    }

    public static function columnAndPermission($whereStr = '' ){

    //    echo '  SELECT `column`.router as `id`,group_concat(`permission`.`role`) as `operation`  FROM `column`  LEFT JOIN `permission` ON `column`.`id` = `permission`.`column_id` GROUP BY `column`.`id` ';
    //   echo '  SELECT `column`.`router` as `id`,group_concat(`permission`.`role`) as `operation`  FROM `column`  LEFT JOIN `permission` ON `column`.`id` = `permission`.`column_id` GROUP BY `column`.`id` ';
      return DB::select('  SELECT `column`.`router` as `id`, IFNULL( group_concat(`permission`.`role`) , "" ) as `operation`  FROM `column`  LEFT JOIN `permission` ON `column`.`id` = `permission`.`column_id` GROUP BY `column`.`id` ');
   
    //    return DB::select(' SELECT `sheetName` as `title`,`sheetName` as `value` FROM `teaching_culture` WHERE `delete_time` is null GROUP BY `sheetName` ORDER BY `id` ASC '  );
       
    }


    public static function  columnPermission(){
  
      return DB::select('  SELECT `column`.*, IFNULL( group_concat(`permission`.`role` ORDER BY `permission`.`id` ASC) , "" ) as `operation`, IFNULL( group_concat(`permission`.`id` ORDER BY `permission`.`id` ASC) , "" ) as `operation_id`  FROM `column`  LEFT JOIN `permission` ON `column`.`id` = `permission`.`column_id` WHERE `column`.`permission_id` = 0  GROUP BY `column`.`id` ');

    }

    public static function  checkRole($column_id,$permission_id){
      // echo "SELECT `column`.`router`, CASE WHEN `permission`.`id` IN (".$permission_id.") THEN GROUP_CONCAT(`permission`.`role`) END as `operation`,CASE WHEN `permission`.`id` IN (".$permission_id.") THEN GROUP_CONCAT(`column_op`.`router`) END as `operation_router` FROM `column` LEFT JOIN `permission` ON `column`.`id` = `permission`.`column_id` LEFT JOIN `column` as `column_op` ON  `column_op`.`permission_id` = `permission`.`id`  WHERE `column`.`id` IN (".$column_id.")  GROUP BY `column`.`id`";
      // echo "SELECT `column`.`router`, CASE WHEN `permission`.`id` IN (".$permission_id.") THEN GROUP_CONCAT(`permission`.`role`) END as `operation`,CASE WHEN `permission`.`id` IN (".$permission_id.") THEN GROUP_CONCAT(`column_op`.`router`) END as `operation_router` FROM `column` LEFT JOIN `permission` ON `column`.`id` = `permission`.`column_id` LEFT JOIN `column` as `column_op` ON  `column_op`.`permission_id` = `permission`.`id`  WHERE `column`.`id` IN (".$column_id.")  GROUP BY `column`.`id`";
      // return  DB::select("SELECT `column`.`router`, CASE WHEN `permission`.`id` IN (".$permission_id.") THEN GROUP_CONCAT(`permission`.`role`) END as `operation`,CASE WHEN `permission`.`id` IN (".$permission_id.") THEN GROUP_CONCAT(`column_op`.`router`) END as `operation_router` FROM `column` LEFT JOIN `permission` ON `column`.`id` = `permission`.`column_id` LEFT JOIN `column` as `column_op` ON  `column_op`.`permission_id` = `permission`.`id`  WHERE `column`.`id` IN (".$column_id.")  GROUP BY `column`.`id`");
      // echo "SELECT `column`.router, GROUP_CONCAT( CASE WHEN FIND_IN_SET(`permission`.`id`,".$column_id.") > 0 THEN `permission`.`role` END ) AS `operation`, GROUP_CONCAT( CASE WHEN FIND_IN_SET(`permission`.`id`,".$column_id.") > 0 THEN `column_op`.`router` END ) AS `operation_router` FROM `column` LEFT JOIN `permission` ON `column`.`id` = `permission`.`column_id` LEFT JOIN `column` AS `column_op` ON `column_op`.`permission_id` = `permission`.`id` WHERE `column`.`id` IN ( ".$column_id." ) GROUP BY `column`.`id`";
    //  echo "SELECT `column`.router, GROUP_CONCAT( CASE WHEN FIND_IN_SET(`permission`.`id`,".$permission_id.") > 0 THEN `permission`.`role` END ) AS `operation`, GROUP_CONCAT( CASE WHEN FIND_IN_SET(`permission`.`id`,".$permission_id.") > 0 THEN `column_op`.`router` END ) AS `operation_router` FROM `column` LEFT JOIN `permission` ON `column`.`id` = `permission`.`column_id` LEFT JOIN `column` AS `column_op` ON `column_op`.`permission_id` = `permission`.`id` WHERE `column`.`id` IN ( ".$column_id." ) GROUP BY `column`.`id`";
      return  DB::select("SELECT `column`.router, GROUP_CONCAT( CASE WHEN FIND_IN_SET(`permission`.`id`,'".$permission_id."') > 0 THEN `permission`.`role` END ) AS `operation`, GROUP_CONCAT( CASE WHEN FIND_IN_SET(`permission`.`id`,'".$permission_id."') > 0 THEN `column_op`.`router` END ) AS `operation_router` FROM `column` LEFT JOIN `permission` ON `column`.`id` = `permission`.`column_id` LEFT JOIN `column` AS `column_op` ON `column_op`.`permission_id` = `permission`.`id` WHERE `column`.`id` IN ( ".$column_id." ) GROUP BY `column`.`id`");
  }


    


    // public static function 
}
